<?php

/**
 * Description of Db
 *
 * @author Clive
 */
class mysql {

    protected $dbDriver = 'mysql';
    protected $dbconn = '';
    protected $table = '';
    protected $field = '';
    protected $where = '';
    protected $orderby = '';
    protected $limit = '';
    public $fetch_array_type = MYSQL_NUM; //MYSQL_ASSOC=1,MYSQL_NUM=2
    public $lastsql = '';

    public function __construct($mysqlconn) {
        $this->dbconn = $mysqlconn;
    }

    private function execute($sql) {
        $this->lastsql = $sql;
        //echo '[' . $sql . ']<br>';
        $result = mysql_query($sql, $this->dbconn) or $this->error();
        return($result);
    }

    private function error() {
        $error = mysql_error();
        if (strstr($error, 'Unknown column')) {
            $error = substr($error, strlen('Unknown column'));
            $arr = explode('in', $error);
            echo "错误报告：在[{$arr[1]}]里没找到名为[{$arr[0]}]的字段<br>";
        } else if (strstr($error, 'in field list is ambiguous')) {
            $name = str::getstr($error, "'", "'");
            echo "错误报告：在多表关联的情况下，同时存在名为【{$name}】的字段<br>";
        } else {
            echo $error . '<br>';
        }
        echo "请检查您的SQL语句：" . $this->lastsql . "<br>";
        die;
    }

    private function setValue(&$val) {
        if (is_string($val)) {
            $val = addslashes($val);
            $val = "'{$val}'";
        } elseif (is_null($val)) {
            $val = 'null';
        } elseif (is_array($val) && array_key_exists('exp', $val)) {
            $val = $val['exp'];
        }
        return $val;
    }

    private function setSign($val) {
        if (strtolower($this->dbDriver) == 'mysql') {
            $val = '`' . trim($val) . '`';
        }
        return $val;
    }

    public function setRow(&$row, $addrow, $filter = '') {
        if ($filter == '') {
            foreach ($addrow as $key => $val) {
                $row[$key] = $val;
            }
        } else {
            $arr = explode(',', $filter);
            foreach ($arr as $val) {
                $row[$val] = $addrow[$val];
            }
        }
    }

    public function init() {
        $this->where = '';
        $this->field = '*';
        $this->limit = '';
        $this->orderby = '';
    }

    public function table($table) {
        $this->init();
        $this->table = $table;
        return($this);
    }

    public function field($field, $field2 = '') {
        if (is_array($field)) {
            $field1 = array();
            foreach ($field as $tablename => $fi) {
                if (!is_array($fi)) {
                    $fi = explode(',', $fi);
                }
                foreach ($fi as $f) {
                    $field1[] = $tablename . '.' . $f;
                }
            }
            $field = implode(',', $field1);
        }
        if ($field2 != '')
            $field.=',' . $field2;
        $this->field = $field;
        return($this);
    }

    public function where($where, $sqlparam) {
        /*
         * where("flag=1 AND id<>{id} AND serverip={ip}", array('id'=>1,'ip'=>'127.0.0.1'))
         */
        if (!is_array($sqlparam)) {
            die('where方法的第二个参数必须是数组！');
        }
        foreach ($sqlparam as $key => $value) {
            if (is_array($value)) {
                $arr = array();
                foreach ($value as $val) {
                    $arr[] = "'" . addslashes($val) . "'";
                }
                $where = str_replace('{' . $key . '}', implode($arr, ','), $where);
            } else {
                //$where = str_replace('\'{' . $key . '}\'', "'" . addslashes($value) . "'", $where);
                //$where = str_replace('{' . $key . '}', "'" . addslashes($value) . "'", $where);
                $where = str_replace('{' . $key . '}', addslashes($value), $where);
            }
        }
        //echo $where;
        $this->where = $where;
        return($this);
    }

    public function orderby($orderby) {
        $this->orderby = $orderby;
        return($this);
    }

    public function order(&$order, $defa = '') {
        if (isset($order['Field']) ? ($order['Field'] == '') : true) {
            if ($defa != '' && !is_array($defa)) {
                $arr = explode(' ', $defa);
                $defa = array();
                $defa['Field'] = $arr[0];
                $defa['Direction'] = strtolower($arr[1]);
            }
            $order['Field'] = $defa['Field'];
            $order['Direction'] = $defa['Direction'];
        }
        if (preg_match("/^(([a-z]{3,12}\.[a-z]{2,10}|[a-z]{2,10})(\s(asc|desc))?\,){1,10}$/", $order['Field'] . ',')) {
            $this->orderby($order['Field']);
        }
        return($this);
    }

    public function limit($limit_count, $limit_start = 0) {
        if ($limit_count > 0) {
            $this->limit = intval($limit_start) . ',' . intval($limit_count);
        }
        return($this);
    }

    /**
     * 增加记录
     */
    public function insert($data) {
        $insert_id = 0;
        if (empty($data))
            return false;
        foreach ($data as $key => $val) {
            if (is_scalar($val)) {
                $fields[] = $this->setSign($key);
                $values[] = $this->setValue($val);
            }
        }
        $fields = implode(',', $fields);
        $values = implode(',', $values);
        $sql = "INSERT INTO `{$this->table}`({$fields})VALUES({$values})";
        if ($this->execute($sql)) {
            $insert_id = mysql_insert_id($this->dbconn);
        }
        return($insert_id);
    }

    /**
     * 修改记录
     */
    public function update($data, $options = array()) {
        if (empty($data))
            return false;
        $set = array();
        foreach ($data as $key => $val) {
            $val = $this->setValue($val);
            if (is_scalar($val)) {
                $set[] = $this->setSign($key) . '=' . $val;
            }
        }
        if (count($set) == 0)
            return false;
        $set = implode(',', $set);
        $sql = "UPDATE ({$this->table}) SET $set";
        if ($this->where > '') {
            $sql.=" WHERE $this->where";
        }
        $this->init();
        return $this->execute($sql);
    }

    public function select($fetch_array_type = 1) {
        $this->fetch_array_type = $fetch_array_type; //MYSQL_ASSOC=1,MYSQL_NUM=2
        $sql = "SELECT {$this->field} FROM {$this->table}";
        if ($this->where > '') {
            $sql.=" WHERE $this->where";
        }
        if ($this->orderby > '') {
            $sql.=" ORDER BY $this->orderby";
        }
        if ($this->limit > '') {
            $sql.=" LIMIT $this->limit";
        }
        if (0)
            $this->init();
        $query_result = $this->execute($sql);
        $output = array();
        if ($this->fetch_array_type == MYSQL_NUM) {
            $output['cols'] = $this->mysql_field_array($query_result); //Columns|fields
        }
        $Items = array();
        //返回的$Items为数据，当TYPE为NUM时只有数据没有字段，如果为ASSOC时包含字段数组
        while ($query_row = mysql_fetch_array($query_result, $this->fetch_array_type)) {
            $Items[] = $query_row;
        }
        mysql_free_result($query_result);
        if ($this->fetch_array_type == MYSQL_ASSOC) {
            $output = $Items;
        } else {
            $output['rows'] = $Items; //Items||rows
        }
        //返回的$output根据fetch_array_type会有不同的结果集（NUM时会返回2个数组“cols列”和“rows行”）
        return($output);
    }

    function mysql_field_array($query) {
        $field = mysql_num_fields($query);
        for ($i = 0; $i < $field; $i++) {
            $names[] = mysql_field_name($query, $i);
        }
        return $names;
    }

    public function getone() {
        $rows = $this->limit(1)->select();
        $row = false;
        if (isset($rows[0])) {
            $row = $rows[0];
        }
        return($row);
    }

    public function getvalue() {
        $value = $this->getone();
        if ($value) {
            foreach ($value as $val) {
                $value = $val;
                break;
            }
        }
        return($value);
    }

    public function count($field = '*') {
        $value = $this->field("count($this->field) AS count1")->getvalue();
        return($value);
    }

    /*
     * 提供分页信息
     */

    private function getpageinfo($totalCount, $pageinfo) {
        if (is_numeric($pageinfo))
            return;
        //获取记录总数
        $pageinfo['totalCount'] = $totalCount;
        //获取要查询的页码
        $pageinfo['pageNum'] = (isset($pageinfo['pageNum']) && intval($pageinfo['pageNum']) >= 1) ? $pageinfo['pageNum'] : 1;
        //获取单页最大数量
        $pageinfo['numPerPage'] = (isset($pageinfo['numPerPage']) && intval($pageinfo['numPerPage']) >= 1) ? $pageinfo['numPerPage'] : 15;
        //计算出分页的页面总数
        $maxPageNum = ceil($pageinfo['totalCount'] / $pageinfo['numPerPage']);
        if ($maxPageNum < 1)
            $maxPageNum = 1;
        //判断是否超过最大页数
        if ($pageinfo['pageNum'] > $maxPageNum)
            $pageinfo['pageNum'] = $maxPageNum;
        return($pageinfo);
    }

    public function page(&$pageinfo) {
        if (is_numeric($pageinfo))
            die('mysql-page错误：$pageinfo数据类型只允许为数组！');
        $field = $this->field;
        $totalCount = $this->field("count(*) AS totalCount")->getvalue();
        $this->field($field);
        $pageinfo = $this->getpageinfo($totalCount, $pageinfo);
        if (isset($pageinfo['numPerPage']) && isset($pageinfo['pageNum'])) {
            $start = $pageinfo['numPerPage'] * ($pageinfo['pageNum'] - 1);
            $this->limit($pageinfo['numPerPage'], $start);
        }
        return($this);
    }

}